﻿/**
 * SQLiteDatabase
 * 这里提供对sqlite的基础访问接口
 * 
 * 
 */
using OfficeOpenXml;
using System.Data;
using System.Data.SQLite;
using System.Diagnostics;
using System.IO;

namespace ZPeng.CSharpPro.DAL.DataBase
{
    public class SQLiteDatabase
    {
        public SQLiteDatabase(string databaseFile)
        {
            _connectionString = $"Data Source={databaseFile}";
        }

        public void Open()
        {
            _connection = new SQLiteConnection(_connectionString);
            _connection.Open();
        }

        public void Close()
        {
            if (_connection != null)
            {
                // 关闭连接逻辑
                _connection.Close();
                _connection.Dispose();
                _connection = null;
            }
        }

        public void ExecuteNonQuery(string query)
        {
            Debug.WriteLine("ExecuteNonQuery:" + query);
            using (var command = new SQLiteCommand(query, _connection))
            {
                command.ExecuteNonQuery();
            }
        }

        public SQLiteDataReader ExecuteReader(string query)
        {
            Debug.WriteLine("ExecuteReader:" + query);
            using (var command = new SQLiteCommand(query, _connection))
            {
                return command.ExecuteReader();
            }
        }



        public void RollBackToDefaultData()
        {
            if (!File.Exists(SourceFilePath))
                throw new Exception("File not Exit:" + SourceFilePath);

            this.Clear();

            ExecuteNonQuery("BEGIN TRANSACTION");

            try
            {
                // 创建数据库文件
                ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
                ExcelPackage excelPackage = new ExcelPackage(new FileInfo(SourceFilePath));

                // 创建数据表-工厂列表
                ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS FactoryList(
                         No TEXT PRIMARY KEY,
                         Name TEXT
                     );");

                // 从excel文件读取工厂列表并写入数据库
                List<string> factoryNoList = new List<string>();
                ExcelWorksheet workSheet = excelPackage.Workbook.Worksheets[0];
                for (int i = 2; i <= workSheet.Dimension.Rows; i++)
                {
                    int idx = Convert.ToInt32(workSheet.Cells[i, 1].Value);
                    string no = workSheet.Cells[i, 2].Text;
                    string name = workSheet.Cells[i, 3].Text;
                    factoryNoList.Add(no);
                    //ExecuteNonQuery($"INSERT INTO FactoryList (Idx, No, Name) VALUES ({idx}, '{no}', '{name}');");
                    ExecuteNonQuery($"INSERT INTO FactoryList ( No, Name) VALUES ( '{no}', '{name}');");
                }

                // 创建数据表-物资总表
                ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS ThingList(
                         No TEXT PRIMARY KEY,
                         Type TEXT,
                         Count INTEGER,
                         FactoryNo TEXT
                     );");

                // 从excel文件读取物资列表并写入数据库
                workSheet = excelPackage.Workbook.Worksheets[1];
                for (int i = 2; i <= workSheet.Dimension.Rows; i++)
                {
                    int idx = Convert.ToInt32(workSheet.Cells[i, 1].Value);
                    string no = workSheet.Cells[i, 2].Text;
                    string type = workSheet.Cells[i, 3].Text;
                    int count = Convert.ToInt32(workSheet.Cells[i, 4].Value);
                    string factoryNo = workSheet.Cells[i, 5].Text;
                    ExecuteNonQuery($"INSERT INTO ThingList ( No, Type, Count, FactoryNo) VALUES ( '{no}', '{type}', {count}, '{factoryNo}');");
                }

                // 创建视图，统计每个工厂拥有的物资数量
                foreach (var no in factoryNoList)
                {
                    ExecuteNonQuery($@"CREATE VIEW IF NOT EXISTS F{no} AS
                         SELECT No, Type, Count
                         FROM ThingList
                         WHERE FactoryNo = '{no}';");
                }

                // 创建大文件表并插入数据
                ExecuteNonQuery(@"CREATE TABLE IF NOT EXISTS BigFiles(
                         FileNo TEXT PRIMARY KEY,
                         OwnerNo TEXT NOT NULL,
                         IsThing INTEGER,
                         IsFactory INTEGER,
                         FileName TEXT,
                         FileBinary BLOB
                     );");

                // 插入大文件表数据
                // 创建SQLiteCommand对象
                foreach (var item in factoryNoList)
                {
                    string fileNo = "F" + item;
                    string ownerNo = item;
                    int isThing = 0;
                    int isFactory = 1;
                    string fileName = "bigfile.file";
                    byte[] fileBinary = File.ReadAllBytes(fileName);

                    string sql = $"INSERT INTO BigFiles (FileNo, OwnerNo, IsThing, IsFactory, FileName, FileBinary) VALUES ('{fileNo}', '{ownerNo}', {isThing}, {isFactory}, '{fileName}', @FileBinary);";

                    using (SQLiteCommand command = new SQLiteCommand(sql, _connection))
                    {
                        // 将二进制数据作为参数传递给SQLiteCommand
                        command.Parameters.Add("@FileBinary", DbType.Binary).Value = fileBinary;

                        // 执行插入操作
                        command.ExecuteNonQuery();
                    }
                }

                // 从表中读取出物资和工厂种类列表。
                SQLiteDataReader reader = ExecuteReader("SELECT DISTINCT Type FROM ThingList;");
                List<string> thingTypeList = new List<string>();
                while (reader.Read())
                {
                    thingTypeList.Add(reader.GetString(0));
                }
                foreach (var item in thingTypeList)
                {
                    string fileNo = "T" + item;
                    string ownerNo = item;
                    int isThing = 1;
                    int isFactory = 0;
                    string fileName = "bigfile.file";
                    byte[] fileBinary = File.ReadAllBytes(fileName);
                    string sql = $"INSERT INTO BigFiles (FileNo, OwnerNo, IsThing, IsFactory, FileName, FileBinary) VALUES ('{fileNo}', '{ownerNo}', {isThing}, {isFactory}, '{fileName}', @FileBinary);";

                    using (SQLiteCommand command = new SQLiteCommand(sql, _connection))
                    {
                        // 将二进制数据作为参数传递给SQLiteCommand
                        command.Parameters.Add("@FileBinary", DbType.Binary).Value = fileBinary;

                        // 执行插入操作
                        command.ExecuteNonQuery();
                    }
                }

                ExecuteNonQuery("COMMIT");
            }
            catch (SQLiteException ex)
            {
                Debug.WriteLine("SQLiteException:" + ex.ToString());
                ExecuteNonQuery("ROLLBACK");
            }
        }

        #region private

        private static string SourceFilePath = "工厂物资统计.xlsx";
        private readonly string _connectionString;
        private SQLiteConnection _connection;

        private void Clear()
        {
            this.Close();

            string filePath = _connectionString.Substring(_connectionString.IndexOf('=') + 1);
            if (File.Exists(filePath))
                File.Delete(filePath);

            this.Open();
        }
        #endregion
    }
}
